﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using KVB.Common;
using Customer_Profile.eCard.DBDao;
using Customer_Profile.Admin;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Configuration;
using System.Collections;
using Microsoft.CSharp;


namespace Customer_Profile.eCard.Settle
{
    public partial class netpay_convert : System.Web.UI.Page
    {
        string tempFileName = "";
        string tempEFilePath = "";
        string tempEFileXPath = "/eCard/TempPayTemplate"; //EXCEL临时文件保存服务器虚拟存放路径 
        decimal iPayLimit;

        string strUserName = "";
        string strUserID = "";
        string strEntity = "";
        string convert_id = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            Comm.CheckSession(this.Page, "/login.aspx", "usermodel");
            if (Session["usermodel"] != null)
            {
                strUserID = ((UserInfoEntity)Session["usermodel"]).UserLoginId;
                strUserName = ((UserInfoEntity)Session["usermodel"]).UserName;
                strEntity = ((UserInfoEntity)Session["usermodel"]).UserOffice;
            }
            else
            {
                Log.WriteDefaultLog("main_01 Page_Load", "Session is null");
                return;
            }
            string strURL = HttpContext.Current.Request.Url.AbsolutePath;
            bool bok = Comm.ifhasright(strUserID, strURL);
            if (!bok)
            {
                Response.Write("你无权访问此页面");
                Response.End();
            }

            iPayLimit = decimal.Parse(KVB.Common.Configuration.GetKeyValue("DownFile", "PayLimit").ToString());

            if (!Page.IsPostBack)
            {
                txtStartAmount.Text = "0";
                txtEndAmount.Text = iPayLimit.ToString();                
            }
            bindConvertList();
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            bindConvertList();
        }

        private void bindConvertList()
        {
            try
            {
                decimal iStartAmount = decimal.Parse(txtStartAmount.Text);
                decimal iEndAmount = decimal.Parse(txtEndAmount.Text);
                string start_due_date = txtcreatedatebegin.Text.Trim();
                string end_due_date = txtcreatedateend.Text.Trim();
                DataSet ds = DealDao.getConvertList(iStartAmount,iEndAmount, start_due_date, end_due_date);
                //DataSet ds = DealDao.getConvertList(iAmount);
                gridConvert.DataSource = ds;
                gridConvert.DataBind();
            }
            catch (Exception ex)
            {
                Response.Write(ex.ToString());
            }
        }

       
        protected void btnConvert_Click(object sender, EventArgs e)
        {           
            try
            {
                lblDownLoad.Text = "";
                string strSettleidList = hidSettleid.Value.Substring(0, hidSettleid.Value.Length - 1);
                //string strConvertTitle = txtConvertTitle.Text.Trim();
                //Log.WriteDefaultLog("需要拆单的交易：", strSettleidList);
                DataSet ds = DealDao.getConvertList_bysettleidlist(strSettleidList);
                ToExcel(ds);               
            }
            catch (Exception ex)
            {
                lblWarning.Text = "转换数据出错,请联系管理员!" + ex.ToString();
                KVB.Common.Log.WriteDefaultLog("netpay_convert_CpartyDao.AddBankTransfer", ex.ToString());                
                Response.Write("<script>alert('转换数据出错！'+" + ex.ToString() + ");</script>");
            }         

            lblDownLoad.Text = "<a href=" + KVB.Common.Configuration.GetKeyValue("DownFile", "WebURL") + tempEFileXPath + " target=_black>下载</a>";
        }

        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        private void ToExcel(DataSet ds)
        {
            string excelTemplateDPath; //EXCEL模板默认服务器物理存放路径 
            Microsoft.Office.Interop.Excel.Application app = null; //EXCEL对象 
            Workbooks workbooks; //工作簿集合 
            _Workbook workbook; //当前工作簿 
            Sheets sheets; //SHEET页集合 
            _Worksheet worksheet; //当前SHEET页 
            try
            {               
                #region 插入Excle 模板中
                #region 读取配置文件中路线模板路径及名称并验证是否存在 //获取配置文件中路线模板路径及名称
                excelTemplateDPath = KVB.Common.Configuration.GetKeyValue("DownFile", "ExcelTemplatePath");
                tempEFilePath = KVB.Common.Configuration.GetKeyValue("DownFile", "TempTemplatePath");
                excelTemplateDPath = excelTemplateDPath + "PayTemplate2003.xls";
                #region//验证EXCEL模板文件是否存在
                if (!System.IO.File.Exists(excelTemplateDPath))
                {
                    lblWarning.Text = "没找到模板";
                    KVB.Common.Log.WriteDefaultLog("模板", "没找到模板");
                    return;
                }
                #endregion//验证EXCEL临时文件夹是否存在

                if (!System.IO.File.Exists(tempEFilePath))
                {
                    Directory.CreateDirectory(tempEFilePath);
                }
                #endregion #region 从配置文件中取列名为填充数据做准备

                #region 启动excel进程并加载模板
                //启动EXCEL进程 
                app = new Microsoft.Office.Interop.Excel.Application();
                if (app == null)
                {
                    lblWarning.Text = "失败！Excel没有启动。";
                    return;
                }
                app.Visible = false;
                app.UserControl = true;
                app.DisplayAlerts = false;
                //加载读取模板 
                workbooks = app.Workbooks;
                workbook = workbooks.Add(excelTemplateDPath);
                sheets = workbook.Worksheets;
                worksheet = (_Worksheet)sheets.get_Item(1);
                #endregion

                #region 填充数据
                int iRow = 0;
                decimal dLimitPer = decimal.Parse(KVB.Common.Configuration.GetKeyValue("DownFile", "LimitPer").ToString());
                //decimal 
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    decimal dAmount = decimal.Parse(ds.Tables[0].Rows[i]["pay_amount"].ToString());
                    if (dAmount >= dLimitPer)
                    {
                        int iSplitCount = Convert.ToInt16(Math.Ceiling(dAmount / dLimitPer));
                        //if (dAmount > iPayLimit)
                        //{
                        //    lblWarning.Text = "数据中有金额大于限额" + iPayLimit.ToString();
                        //}
                        //else
                        //{
                            for (int iSplit = 1; iSplit <= iSplitCount; iSplit++)
                            {
                                decimal dTempAmount;
                                if (iSplit < iSplitCount)
                                {
                                    dTempAmount = dLimitPer;                                     //金额	
                                }
                                else
                                {
                                    dTempAmount = dAmount - dLimitPer * (iSplitCount - 1);
                                }

                                #region 插入Excel模板
                                worksheet.Cells[iRow + 2, 1] = (iRow + 1).ToString();
                                worksheet.Cells[iRow + 2, 2] = ds.Tables[0].Rows[i]["account_no"].ToString().Trim();              //银行账号
                                worksheet.Cells[iRow + 2, 3] = ds.Tables[0].Rows[i]["account_name"].ToString().Trim();            //开户名
                                worksheet.Cells[iRow + 2, 4] = ds.Tables[0].Rows[i]["bank_name"].ToString().Trim();               //开户行
                                worksheet.Cells[iRow + 2, 5] = ds.Tables[0].Rows[i]["bank_branch"].ToString().Trim();             //分行
                                worksheet.Cells[iRow + 2, 6] = ds.Tables[0].Rows[i]["bank_sub_branch"].ToString().Trim();         //支行
                                worksheet.Cells[iRow + 2, 7] = "私";                                             //帐户类型	                                
                                worksheet.Cells[iRow + 2, 8] = dTempAmount;                                      //金额	                                
                                worksheet.Cells[iRow + 2, 9] = "CNY";                                            //币种	
                                worksheet.Cells[iRow + 2, 10] = ds.Tables[0].Rows[i]["location"].ToString().Trim();               //省	
                                worksheet.Cells[iRow + 2, 11] = ds.Tables[0].Rows[i]["city"].ToString().Trim();                   //市	 
                                #endregion
                                iRow++;
                            }
                        //}
                    }
                    else
                    {
                        #region 插入Excel模板
                        worksheet.Cells[iRow + 2, 1] = (iRow + 1).ToString();
                        worksheet.Cells[iRow + 2, 2] = ds.Tables[0].Rows[i]["account_no"].ToString().Trim();                 //银行账号
                        worksheet.Cells[iRow + 2, 3] = ds.Tables[0].Rows[i]["account_name"].ToString().Trim();               //开户名
                        worksheet.Cells[iRow + 2, 4] = ds.Tables[0].Rows[i]["bank_name"].ToString().Trim();                  //开户行
                        worksheet.Cells[iRow + 2, 5] = ds.Tables[0].Rows[i]["bank_branch"].ToString().Trim();                //分行
                        worksheet.Cells[iRow + 2, 6] = ds.Tables[0].Rows[i]["bank_sub_branch"].ToString().Trim();            //支行
                        worksheet.Cells[iRow + 2, 7] = "私";                                                                 //帐户类型	
                        worksheet.Cells[iRow + 2, 8] = dAmount;                                                              //金额
                        worksheet.Cells[iRow + 2, 9] = "CNY";                                                                //币种	
                        worksheet.Cells[iRow + 2, 10] = ds.Tables[0].Rows[i]["location"].ToString().Trim();                  //省	
                        worksheet.Cells[iRow + 2, 11] = ds.Tables[0].Rows[i]["city"].ToString().Trim();                      //市	 
                        #endregion
                        iRow++;
                    }
                }
                #endregion

                #region 保存临时文件
                //worksheet.Columns.AutoFit(); //设置自动列宽 
                try
                {
                    tempFileName = "NetPay" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                    tempEFilePath += tempFileName;
                    tempEFileXPath += "/" + tempFileName;
                    worksheet.SaveAs(tempEFilePath, Missing.Value, Missing.Value, Missing.Value,
                            Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange,
                            Missing.Value, Missing.Value, Missing.Value);
                    DeleteTempFiles(tempEFilePath); //清除临时文件 
                }
                catch (Exception ex)
                {
                    lblWarning.Text += "失败！excel保存出错！";
                    lblDownLoad.Text = "";
                    KVB.Common.Log.WriteDefaultLog("Excel保存出错", ex.ToString());
                    throw ex;
                }
                #endregion


                Response.Write("<script>alert('转换数据成功！');</script>");
                lblWarning.Text = "转换数据成功";
                #endregion
            }
            catch (Exception ex)
            {
                Log.WriteDefaultLog("Excel转换：", ex.ToString());
                Response.Write(ex.ToString());
                throw ex;
                
            }
            finally
            {
                ExcelDispose(app);
            }
            
        }
        /// /// 关闭Excel进程 /// /// /// /// 
        protected void ExcelDispose(_Application CurExcel)
        {
            try
            {
                if (CurExcel != null)
                {
                    CurExcel.Workbooks.Close();
                    CurExcel.Quit();

                    #region 强行杀死打开的Excel进程
                    IntPtr excelIPtr = new IntPtr(CurExcel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 
                    int proID = 0;
                    GetWindowThreadProcessId(excelIPtr, out proID); //得到本进程唯一标志k 
                    System.Diagnostics.Process pro = System.Diagnostics.Process.GetProcessById(proID); //得到对进程k的引用 pro.Kill(); 
                    //关闭进程k #endregion CurExcel = null; 
                    #endregion
                }
            }
            catch (Exception ex) 
            {
                //ShowMessage("在释放内存时发生错误：" + ex.ToString());
                KVB.Common.Log.WriteDefaultLog("ExcelDisposet", ex.Message);
            }
        }
        /// /// 删除之前无用的临时文件 /// /// 
        protected void DeleteTempFiles(string iFileName)
        {
            FileInfo f = new FileInfo(iFileName);
            DirectoryInfo dir = f.Directory;
            foreach (FileInfo tfile in dir.GetFiles())
            {
                try { tfile.Delete(); }
                catch { }
            }
        }
    }
}